﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Aop.Api.Domain;
using Dapper;
using Logistics.Model;
using Microsoft.Data.SqlClient;
using NLog;

namespace Logistics.Web
{
    public class DapperHelper
    {
        private const string connString = "server=.;database=FaceDB;uid=sa;pwd=111111";
        //private const string connString = "server=iZfwi3l9ql8f79Z;database=CodePlatform;user=sa;password=Sso09!19.z6X)12;";

        public static int AddPayReturnUrl(AlipayReturnInfo alipayReturnInfo)
        {
            var i = 0;
            try
            {
                using (IDbConnection conn = new SqlConnection(connString))
                {
                    conn.Open();
                    string strSql = "insert into AlipayReturnInfo(Charset,OutTradeNo,Method,TotalAccount,TradeNo,AuthAppId,Version,AppId,SellerId,TimeStamp,Remarks,CreateTime) values('" + alipayReturnInfo.Charset + "', '" + alipayReturnInfo.OutTradeNo + "', '" + alipayReturnInfo.Method + "', '" + alipayReturnInfo.TotalAccount + "', '" + alipayReturnInfo.TradeNo + "', '" + alipayReturnInfo.AuthAppId + "', '" + alipayReturnInfo.Version + "', '" + alipayReturnInfo.AppId + "', '" + alipayReturnInfo.SellerId + "', '" + alipayReturnInfo.TimeStamp + "', '" + alipayReturnInfo.Remarks + "', getdate())";
                    i = conn.Execute(strSql);
                }
            }
            catch (Exception ex)
            {
                LogManager.GetCurrentClassLogger().Error($"插入数据库报错啦：" + ex.Message);
            }
            return i;
        }

        public static int AddOrder(AlipayTradePagePayModel model)
        {
            var i = 0;
            try
            {
                using (IDbConnection conn = new SqlConnection(connString))
                {
                    conn.Open();
                    string strSql = "insert into [order] (Body,Subject,TotalAmount,OutTradeNo,ProductCode,PayStatus,Remarks,CreateTime) values('" + model.Body + "', '" + model.Subject + "', '" + model.TotalAmount + "', '" + model.OutTradeNo + "', '" + model.ProductCode + "','0', '备注信息', getdate())";
                    i = conn.Execute(strSql);
                }
            }
            catch (Exception ex)
            {
                LogManager.GetCurrentClassLogger().Error($"添加订单报错啦：" + ex.Message);
            }
            return i;
        }

        /// <summary>
        /// 修改订单状态
        /// </summary>
        /// <param name="OutTradeNo"></param>
        /// <param name="TotalAmount"></param>
        /// <param name="PayStatus">0:默认，1：成功，2：失败</param>
        /// <returns></returns>
        public static int UpdateOrderStatus(string OutTradeNo, string TotalAmount, int PayStatus)
        {
            var i = 0;
            try
            {
                using (IDbConnection conn = new SqlConnection(connString))
                {
                    conn.Open();
                    string strSql = "update [order] set paystatus = '" + PayStatus + "' where OutTradeNo='" + OutTradeNo + "' and TotalAmount = '" + TotalAmount + "'";
                    i = conn.Execute(strSql);
                }
            }
            catch (Exception ex)
            {
                LogManager.GetCurrentClassLogger().Error($"修改订单状态报错啦：" + ex.Message);
            }
            return i;
        }

        /// <summary>
        /// 验证订单状态
        /// </summary>
        /// <param name="OutTradeNo"></param>
        /// <param name="TotalAmount"></param>
        /// <param name="PayStatus">0:默认，1：成功，2：失败</param>
        /// <returns></returns>
        public static string CheckPaymen(string OutTradeNo, string TotalAmount)
        {
            var result = string.Empty;
            try
            {
                using (IDbConnection conn = new SqlConnection(connString))
                {
                    conn.Open();
                    string strSql = "select paystatus from [order] where OutTradeNo='" + OutTradeNo + "' and TotalAmount = '" + TotalAmount + "'";
                    LogManager.GetCurrentClassLogger().Error($"验证订单状态sql：" + strSql);
                    result = conn.ExecuteScalar<string>(strSql);
                    result = string.IsNullOrWhiteSpace(result) ? "0" : result;
                }
            }
            catch (Exception ex)
            {
                LogManager.GetCurrentClassLogger().Error($"修改订单状态报错啦：" + ex.Message);
            }
            return result;
        }

        /// <summary>
        /// 订单退款列表
        /// </summary>
        /// <returns></returns>
        public static List<RefundInfo> RefundList()
        {
            var result = new List<RefundInfo>();
            try
            {
                using (IDbConnection conn = new SqlConnection(connString))
                {
                    conn.Open();
                    string strSql = "select t1.id,t1.Body,t1.Subject,t1.TotalAmount, t2.OutTradeNo,TradeNo,t1.PayStatus,t1.CreateTime from[Order] t1 join  AlipayReturnInfo t2 on t1.OutTradeNo = t2.OutTradeNo where t1.PayStatus = '1'";
                    LogManager.GetCurrentClassLogger().Error($"订单退款列表Sql：" + strSql);
                    result = conn.Query<RefundInfo>(strSql).ToList();
                }
            }
            catch (Exception ex)
            {
                LogManager.GetCurrentClassLogger().Error($"修改订单状态报错啦：" + ex.Message);
            }
            return result;
        }

        /// <summary>
        /// 修改订单退款
        /// </summary>
        /// <param name="outTradeNo"></param>
        /// <param name="totalAmount"></param>
        /// <param name="payStatus"></param>
        /// <returns></returns>
        public static List<RefundInfo> ModifyOrderStatus(string outTradeNo, string totalAmount)
        {
            var result = new List<RefundInfo>();
            try
            {
                using (IDbConnection conn = new SqlConnection(connString))
                {
                    conn.Open();
                    string strSql = "update [Order] set PayStatus='9' where OutTradeNo='"+ outTradeNo + "' and TotalAmount='"+totalAmount+"'";
                    LogManager.GetCurrentClassLogger().Error($"订单退款列表Sql：" + strSql);
                    result = conn.Query<RefundInfo>(strSql).ToList();
                }
            }
            catch (Exception ex)
            {
                LogManager.GetCurrentClassLogger().Error($"修改订单状态报错啦：" + ex.Message);
            }
            return result;
        }
    }
}
